﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Data.Linq;
using DataObject.Database;
using DataObject.Core;
using DataObject.Helpers;
using DataObject.Model;

namespace DataObject.DataAccess
{
    public class ServiceDAO : DataBaseProcessBase<Service, OBSDataContext>
    {
        #region Singleton
        public static ServiceDAO CreateInstant()
        {
            return new ServiceDAO();
        }
        #endregion

        public IList<Service> GetServices(
            string serviceCode, string serviceName,
            int? Page, int? Row, out int Count, HashSet<string> SortClause)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                db.DeferredLoadingEnabled = false; //disabling will turn off deferred loading (lazy fetch)

                try
                {
                    var query = (from x in db.Services where x.Active select x);
                    if (!String.IsNullOrEmpty(serviceCode))
                        query = query.Where(x => x.ServiceCode.ToLower().Equals(serviceCode.ToLower()));
                    if (!String.IsNullOrEmpty(serviceName))
                        query = query.Where(x => x.ServiceName.ToLower().Contains(serviceName));
                    for (int i = SortClause.Count - 1; i >= 0; i--)
                        query = query.OrderBy(SortClause.ElementAt(i));
                    Count = query.Count();
                    query = query.OrderBy(x => x.ServiceCode);
                    if (Page.HasValue && Row.HasValue)
                        return query.PageIQuery(Page.Value, Row.Value).ToList();
                    return query.ToList();
                }
                catch
                {
                    Count = 0;
                    return null;
                }
            }
        }

        public IList<ReservationServiceDTO> GetServicesBy(int routeNumber, char? subRouteCode, DateTime boardDate)
        {
            IList<ReservationServiceDTO> serviceList = null;
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                db.DeferredLoadingEnabled = false; //disabling will turn off deferred loading (lazy fetch)

                var query = db.RouteServices.Where(x => x.Route.Active && x.Route.RouteNumber == routeNumber);

                // Clear subRoute to get only Route
                if (subRouteCode.HasValue && subRouteCode != ' ')
                    query = query.Where(x => x.Route.SubRouteCode == subRouteCode);
                else
                    query = query.Where(x => x.Route.SubRouteCode == null);

                // Sort
                query = query.OrderBy(b => b.DayOfRoute);

                serviceList = new List<ReservationServiceDTO>();
                foreach (var item in query)
                {
                    item.Service = db.Services.SingleOrDefault(x => x.ServiceID == item.ServiceID);
                    item.ProviderEntity = db.ProviderEntities.SingleOrDefault(x => x.ProviderID == item.ProviderID);
                    var packPrice = Decimal.Zero;
                    var unitPrice = Decimal.Zero;
                    var numInPackage = 0;
                    var currency = 0;
                    var serviceProvider = db.ServiceProviders.SingleOrDefault(x => x.ServiceID == item.ServiceID && x.ProviderID == item.ProviderID);
                    if (serviceProvider != null)
                    {
                        packPrice = serviceProvider.PackagePrice;
                        unitPrice = serviceProvider.UnitPrice;
                        numInPackage = serviceProvider.NumInPackage;
                        currency = serviceProvider.Currency;
                    }
                    else
                    {
                        packPrice = item.Service != null ? item.Service.PackagePrice : 0;
                        unitPrice = item.Service != null ? item.Service.UnitPrice : 0;
                        numInPackage = item.Service != null ? item.Service.NumInPackage : 0;
                        currency = item.Service != null ? item.Service.Currency : 0;
                    }

                    serviceList.Add(new ReservationServiceDTO
                    {
                        Date = boardDate.AddDays(item.DayOfRoute),
                        Program = item.Program,
                        ServiceID = item.ServiceID,
                        ProviderID = item.ProviderID,
                        ServiceType = item.ServiceType,
                        Service = ServiceMapper.ToSingle(item.Service),
                        Provider = ProviderMapper.ToSingle(item.ProviderEntity),
                        PackagePrice = packPrice,
                        UnitPrice = unitPrice,
                        Currency = currency,
                        Rate = 0,
                        NumInPackage = numInPackage
                    });
                }
                return serviceList;
            }
        }

        public IList<ReservationServiceDTO> GetServicesBy(Guid reservationID)
        {
            IList<ReservationServiceDTO> serviceList = null;
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                db.DeferredLoadingEnabled = false; //disabling will turn off deferred loading (lazy fetch)

                var query = (from x in db.ReservationServices
                             where x.ReservationID == reservationID
                             select x);
                query = query.OrderBy(b => b.Date);
                serviceList = new List<ReservationServiceDTO>();
                foreach (var item in query)
                {
                    item.Service = db.Services.SingleOrDefault(x => x.ServiceID == item.ServiceID);
                    item.ProviderEntity = db.ProviderEntities.SingleOrDefault(x => x.ProviderID == item.ProviderID);
                    serviceList.Add(ReservationServiceMapper.ToBusinessObject(item));
                }
                return serviceList;
            }
        }

        #region Get Methods
        public ServiceDTO Get(System.Guid serviceID)
        {
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                return ServiceMapper.ToBusinessObjectHasChildren(biocode.Services.SingleOrDefault(a => a.ServiceID == serviceID), typeof(ServiceProvider));
            }
        }

        public ServiceDTO Get(string serviceCode)
        {
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                return ServiceMapper.ToBusinessObject(biocode.Services.SingleOrDefault(a => a.ServiceCode == serviceCode));
            }
        }

        public IList<Service> GetAll()
        {
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                return biocode.Services.Where(x => x.Active).OrderBy(x => x.ServiceName).ToList();
            }
        }
        #endregion

        #region Update Methods

        public bool Insert(Service entity)
        {
            bool result = false;
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                biocode.Services.InsertOnSubmit(entity);
                try
                {
                    biocode.SubmitChanges();
                    result = true;
                }
                catch (ChangeConflictException)
                {
                    //  A possible concurrency exception occurred.  Let's see if
                    //  we can resolve it.
                    foreach (var conflict in biocode.ChangeConflicts)
                    {
                        conflict.Resolve(RefreshMode.KeepCurrentValues);
                    }
                    try
                    {
                        biocode.SubmitChanges();
                        result = true;
                    }
                    catch (ChangeConflictException)
                    {
                        //  It didn't work, so throw a new exception.
                        // throw new Exception("A concurrency error occurred!");
                    }
                    catch (Exception)
                    {
                        // throw new Exception("There was an error saving this record!");
                    }
                }
            }

            return result;
        }

        public bool Insert(ServiceDTO dto)
        {
            bool result = false;
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                // Prepare data
                dto.Active = true;

                var entity = ServiceMapper.ToEntity(new Service(), dto);
                foreach (var serviceProvider in dto.ServiceProviderCollection)
                {
                    serviceProvider.ServiceProviderID = Helper.NewSeqGuid();
                    serviceProvider.ServiceID = dto.ServiceID;
                    entity.ServiceProviders.Add(ServiceProviderMapper.ToEntity(new ServiceProvider(), serviceProvider));
                }

                // Insert entity
                biocode.Services.InsertOnSubmit(entity);

                try
                {
                    biocode.SubmitChanges();
                    result = true;
                }
                catch (ChangeConflictException)
                {
                    //  A possible concurrency exception occurred.  Let's see if
                    //  we can resolve it.
                    foreach (var conflict in biocode.ChangeConflicts)
                    {
                        conflict.Resolve(RefreshMode.KeepCurrentValues);
                    }
                    try
                    {
                        biocode.SubmitChanges();
                        result = true;
                    }
                    catch (ChangeConflictException)
                    {
                        //  It didn't work, so throw a new exception.
                        // throw new Exception("A concurrency error occurred!");
                    }
                    catch (Exception)
                    {
                        // throw new Exception("There was an error saving this record!");
                    }
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.Message);
                }
            }

            return result;
        }

        public bool Insert(IList<Service> list)
        {
            bool result = false;
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                biocode.Services.InsertAllOnSubmit(list);
                try
                {
                    biocode.SubmitChanges();
                    result = true;
                }
                catch (ChangeConflictException)
                {
                    //  A possible concurrency exception occurred.  Let's see if
                    //  we can resolve it.
                    foreach (var conflict in biocode.ChangeConflicts)
                    {
                        conflict.Resolve(RefreshMode.KeepCurrentValues);
                    }
                    try
                    {
                        biocode.SubmitChanges();
                        result = true;
                    }
                    catch (ChangeConflictException)
                    {
                        //  It didn't work, so throw a new exception.
                        // throw new Exception("A concurrency error occurred!");
                    }
                    catch (Exception)
                    {
                        // throw new Exception("There was an error saving this record!");
                    }
                }
            }

            return result;
        }

        public bool Update(ServiceDTO dto)
        {
            bool result = false;
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                var entity = biocode.Services.Where(x => x.ServiceID == dto.ServiceID).SingleOrDefault();
                if (entity != null)
                {
                    biocode.ServiceProviders.DeleteAllOnSubmit(entity.ServiceProviders);

                    dto.Active = true;
                    ServiceMapper.ToEntity(entity, dto);
                    foreach (var serviceProvider in dto.ServiceProviderCollection.Where(x => !x.IsDeleted))
                    {
                        serviceProvider.ServiceProviderID = Helper.NewSeqGuid();
                        serviceProvider.ServiceID = dto.ServiceID;
                        entity.ServiceProviders.Add(ServiceProviderMapper.ToEntity(new ServiceProvider(), serviceProvider));
                    }

                    try
                    {
                        biocode.SubmitChanges();
                        result = true;
                    }
                    catch (ChangeConflictException)
                    {
                        //  A possible concurrency exception occurred.  Let's see if
                        //  we can resolve it.
                        foreach (var conflict in biocode.ChangeConflicts)
                        {
                            conflict.Resolve(RefreshMode.KeepCurrentValues);
                        }
                        try
                        {
                            biocode.SubmitChanges();
                            result = true;
                        }
                        catch (ChangeConflictException)
                        {
                            //  It didn't work, so throw a new exception.
                            // throw new Exception("A concurrency error occurred!");
                        }
                        catch (Exception)
                        {
                            // throw new Exception("There was an error saving this record!");
                        }
                    }
                }
            }

            return result;
        }

        public Service ToEntity(Service entity, Service businessobject)
        {
            if (businessobject == null)
            {
                return null;
            }
            entity.Active = businessobject.Active;
            entity.NightFlag = businessobject.NightFlag;
            entity.ChoiceOption = businessobject.ChoiceOption;
            entity.Description = businessobject.Description;
            entity.ModifiedBy = businessobject.ModifiedBy;
            entity.ModifiedDate = businessobject.ModifiedDate;
            entity.NumInPackage = businessobject.NumInPackage;
            entity.OtherUom = businessobject.OtherUom;
            entity.PackagePrice = businessobject.PackagePrice;
            entity.ServiceCode = businessobject.ServiceCode;
            entity.ServiceID = businessobject.ServiceID;
            entity.ServiceName = businessobject.ServiceName;
            entity.Unit = businessobject.Unit;
            entity.Currency = businessobject.Currency;
            entity.UnitPrice = businessobject.UnitPrice;
            return entity;
        }

        public bool Update(Service businessobject)
        {
            bool result = false;

            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                var entity = biocode.Services.SingleOrDefault(x => x.ServiceID == businessobject.ServiceID);

                if (entity != null)
                {
                    entity = ToEntity(entity, businessobject);
                }

                try
                {
                    biocode.SubmitChanges();
                    result = true;
                }
                catch (ChangeConflictException)
                {
                    //  A possible concurrency exception occurred.  Let's see if
                    //  we can resolve it.
                    foreach (var conflict in biocode.ChangeConflicts)
                    {
                        conflict.Resolve(RefreshMode.KeepCurrentValues);
                    }
                    try
                    {
                        biocode.SubmitChanges();
                        result = true;
                    }
                    catch (ChangeConflictException)
                    {
                        //  It didn't work, so throw a new exception.
                        // throw new Exception("A concurrency error occurred!");
                    }
                    catch (Exception)
                    {
                        // throw new Exception("There was an error saving this record!");
                    }
                }
            }

            return result;
        }

        public bool Delete(Guid serviceID)
        {
            bool result = false;
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                var entity = biocode.Services.Where(x => x.ServiceID == serviceID).SingleOrDefault();
                if (entity != null)
                {
                    if (biocode.ReservationServices.Count(x => x.ServiceID == serviceID) > 0 ||
                        biocode.RouteServices.Count(x => x.ServiceID == serviceID) > 0)
                        entity.Active = false;
                    else
                    {
                        biocode.ServiceProviders.DeleteAllOnSubmit(entity.ServiceProviders);
                        biocode.Services.DeleteOnSubmit(entity);
                    }

                    try
                    {
                        biocode.SubmitChanges();
                        result = true;
                    }
                    catch (ChangeConflictException)
                    {
                        //  A possible concurrency exception occurred.  Let's see if
                        //  we can resolve it.
                        foreach (var conflict in biocode.ChangeConflicts)
                        {
                            conflict.Resolve(RefreshMode.KeepCurrentValues);
                        }
                        try
                        {
                            biocode.SubmitChanges();
                            result = true;
                        }
                        catch (ChangeConflictException)
                        {
                            //  It didn't work, so throw a new exception.
                            // throw new Exception("A concurrency error occurred!");
                        }
                        catch (Exception)
                        {
                            // throw new Exception("There was an error saving this record!");
                        }
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(ex.Message);
                    }
                }
            }

            return result;
        }

        public bool Inactive(Service entity)
        {
            bool result = false;

            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                var item = biocode.Services.SingleOrDefault(x => x.ServiceID == entity.ServiceID);
                item.Active = false;

                try
                {
                    biocode.SubmitChanges();
                    result = true;
                }
                catch (ChangeConflictException)
                {
                    //  A possible concurrency exception occurred.  Let's see if
                    //  we can resolve it.
                    foreach (var conflict in biocode.ChangeConflicts)
                    {
                        conflict.Resolve(RefreshMode.KeepCurrentValues);
                    }
                    try
                    {
                        biocode.SubmitChanges();
                        result = true;
                    }
                    catch (ChangeConflictException)
                    {
                        //  It didn't work, so throw a new exception.
                        // throw new Exception("A concurrency error occurred!");
                    }
                    catch (Exception)
                    {
                        // throw new Exception("There was an error saving this record!");
                    }
                }
            }

            return result;
        }

        #endregion
    }
}
